package edu.columbia.cloudbox.policy;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import edu.columbia.cloudbox.io.FileType;

public class CBDBHelper {
	
	private static final String LOG_TAG = "CBDBHelper";
	
	private static final String DATABASE_NAME = "cloudbox.db";
	
	private static final int DATABASE_VERSION = 1;
	
	private static final String TABLE_NAME = "file_log";
	
	private static final String FILE_ID = "id";
	private static final String FILE_NAME = "f_name";
	private static final String FILE_TYPE = "f_type";
	private static final String FILE_SIZE = "f_size";
	private static final String TIME_SAVE = "time_save";
	private static final String TIME_LAST_ACC = "time_last_acc";
	private static final String TIME_DELETE = "time_delete";
	private static final String NUM_TOTAL_ACC = "num_total_acc";
	private static final String IS_DELETED = "is_deleted";
	
	private Context context;
	
	private SQLiteDatabase db;
	
	private static CBDBOpenHelper openHelper;
	
	public CBDBHelper(Context c) {
		context = c;
		openHelper = new CBDBOpenHelper(context);
		db = openHelper.getWritableDatabase();
	}
	
	/**
	 * Insert into db when download a file from S3
	 * 
	 * @param file
	 */
	public void insertFile(FileType file) {
		String fileKey = file.getKey();
		String[] args = {fileKey};
		Cursor cursor = db.query(TABLE_NAME, null, FILE_NAME + "=?", args, null, null, null);
		ContentValues values = new ContentValues();
		if (cursor.getCount() > 0) {
			values.put(TIME_SAVE, System.currentTimeMillis());
			db.update(TABLE_NAME, values, FILE_NAME + "=" + "'" + fileKey + "'", null);
			Log.i(LOG_TAG, "Updated " + FILE_NAME + " save_time");
		} else {
			values.put(FILE_NAME, fileKey);
			values.put(FILE_TYPE, file.getType());
			values.put(FILE_SIZE, file.getSize());
			values.put(TIME_SAVE, System.currentTimeMillis());
			values.put(NUM_TOTAL_ACC, 0);
			values.put(IS_DELETED, "N");
			long rowId = db.insert(TABLE_NAME, null, values);
			Log.i(LOG_TAG, "Inserted file " + fileKey + " into row " + rowId);
		}
	}
	
	/**
	 * Insert into db when download a folder from S3
	 * 
	 * @param files
	 */
	public void insertFolder(List<FileType> files) {
		Log.i(LOG_TAG, "Folder size: " + files.size());
		Iterator<FileType> it = files.iterator();
		while(it.hasNext()) {
			FileType file = it.next();
			if (file.isFolder())
				continue;
			insertFile(file);
		}
	}
	
	/**
	 * Update column time_delete in db when delete a file from local storage
	 * 
	 * @param fileName
	 */
	public void deleteFile(String fileName) {
		ContentValues values =  new ContentValues();
		values.put(TIME_DELETE, System.currentTimeMillis());
		values.put(IS_DELETED, "Y");
		Log.i(LOG_TAG, "Deleting file " + fileName + " from table " + TABLE_NAME + "...");
		long rowId = db.update(TABLE_NAME, values, FILE_NAME + "=" + "'" + fileName + "'", null);
		Log.i(LOG_TAG, "Updated row " + rowId);
	}
	
	/**
	 * Update column time_delete in db when delete a folder from local storage
	 * 
	 * @param fileNames
	 */
	public void deleteFolder(List<String> fileNames) {
		Iterator<String> it = fileNames.iterator();
		while (it.hasNext()) {
			String fileName = it.next();
			deleteFile(fileName);
		}
	}
	
	/**
	 * Update column last_read_time, num_total_read
	 * @param fileName
	 */
	public void readFile(String fileName) {
		Cursor cursor = db.query(TABLE_NAME, new String[]{NUM_TOTAL_ACC}, FILE_NAME + "=?", new String[]{fileName}, null, null, null);
		Log.i(LOG_TAG, "column index: " + cursor.getColumnIndexOrThrow(NUM_TOTAL_ACC));
		cursor.moveToFirst();
		int num_acc = cursor.getInt(0);
		ContentValues values =  new ContentValues();
		values.put(TIME_LAST_ACC, System.currentTimeMillis());
		values.put(NUM_TOTAL_ACC, ++num_acc);
		Log.i(LOG_TAG, "Updating file " + fileName + " from table " + TABLE_NAME + "...");
		long rowId = db.update(TABLE_NAME, values, FILE_NAME + "=" + "'" + fileName + "'", null);
		Log.i(LOG_TAG, "Updated row " + rowId);
	}
	
	/**
	 * 
	 * @param num
	 * 			total number of rows to get
	 * @return
	 * 			A list of files that have largest size
	 */
	public List<String> querySizeFirst(int num) {
		List<String> files = new ArrayList<String>();
		
		Cursor cursor = null;
		if (num > 0)
			cursor = db.query(TABLE_NAME, new String[] { FILE_NAME },
					IS_DELETED + "=?", new String[] { "Y" }, null, null,
					FILE_SIZE, String.valueOf(num));
		else
			cursor = db.query(TABLE_NAME, new String[] { FILE_NAME },
					IS_DELETED + "=?", new String[] { "Y" }, null, null,
					FILE_SIZE);
		
		if (cursor.getCount() > 0) {
			do {
				files.add(cursor.getString(0));
			} while (cursor.moveToNext());
		}while (cursor.moveToNext());
		
		return files;
	}
	
	/**
	 * 
	 * @param num
	 * 			total number of rows to get
	 * @return
	 * 			A list of files that are accessed most recently
	 */
	public List<String> queryMostRecent(int num) {
		List<String> files = new ArrayList<String>();
		
		Cursor cursor = null;
		if (num > 0)
			cursor = db.query(TABLE_NAME, new String[] { FILE_NAME },
					IS_DELETED + "=?", new String[] { "Y" }, null, null,
					TIME_LAST_ACC, String.valueOf(num));
		else
			cursor = db.query(TABLE_NAME, new String[] { FILE_NAME },
					IS_DELETED + "=?", new String[] { "Y" }, null, null,
					TIME_LAST_ACC);
		
		cursor.moveToFirst();
		
		if (cursor.getCount() > 0) {
			do {
				files.add(cursor.getString(0));
			} while (cursor.moveToNext());
		}
		
		return files;
	}
	
	/**
	 * 
	 * @param num
	 * 			total number of rows to get
	 * @return
	 * 			A list of file name are most accessed
	 */
	public List<String> queryMostAccessed(int num) {
		List<String> files = new ArrayList<String>();
		
		Cursor cursor = null;
		if (num > 0)
			cursor = db.query(TABLE_NAME, new String[] { FILE_NAME },
					IS_DELETED + "=?", new String[] { "Y" }, null, null,
					NUM_TOTAL_ACC, String.valueOf(num));
		else
			cursor = db.query(TABLE_NAME, new String[] { FILE_NAME },
					IS_DELETED + "=?", new String[] { "Y" }, null, null,
					NUM_TOTAL_ACC);
		
		cursor.moveToFirst();

		if (cursor.getCount() > 0) {
			do {
				files.add(cursor.getString(0));
			} while (cursor.moveToNext());
		}
		return files;
	}
	
	public void close() {
		openHelper.close();
	}
	
	private static class CBDBOpenHelper extends SQLiteOpenHelper {
		
		CBDBOpenHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			Log.i(LOG_TAG, "Creating table " + TABLE_NAME + "...");
			StringBuffer sql = new StringBuffer();
			sql.append("CREATE TABLE ");
			sql.append(TABLE_NAME);
			sql.append("(");
			sql.append("id INTEGER PRIMARY KEY AUTOINCREMENT,");
			sql.append("f_name TEXT,");
			sql.append("f_type TEXT,");
			sql.append("f_size REAL,");
			sql.append("time_save INTEGER,");
			sql.append("time_last_acc INTEGER,");
			sql.append("time_delete INTEGER,");
			sql.append("num_total_acc INTEGER,");
			sql.append("is_deleted TEXT");
			sql.append(")");
			db.execSQL(sql.toString());
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
	        onCreate(db);
		}
	}
}
